package com.cennavi.minemap.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.cennavi.minemap.bean.TokenBean;
import com.cennavi.minemap.bean.TokenSolusIdBean;
import com.cennavi.minemap.bean.TokenWithSoluBean;

@Component
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class })
public class TokenDao extends AbstractDao {
	
	private String columns = "id, name, type, userId, createTime";
	
	public int queryTokenCount(int userId) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT count(*) FROM MM_TOKEN WHERE userId = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { userId }, Integer.class);
	}

	public void insert(final TokenBean tokenBean) {
		jdbcTemplate.setDataSource(pgDataSource);
		final String sql = "INSERT INTO MM_TOKEN (id, name, type, userId, createTime) " + "VALUES(?, ?, ?, ?, ?)";
		jdbcTemplate.update(sql, new Object[] { tokenBean.getId(), tokenBean.getName(), tokenBean.getType(),
				tokenBean.getUserId(), tokenBean.getCreateTime() });
	}
	public int update(final TokenBean tokenBean) {
		jdbcTemplate.setDataSource(pgDataSource);

		String setSql = super.getSetSql(tokenBean);
		String sql = "UPDATE MM_TOKEN SET " + setSql + " WHERE id = ?";
		return jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, tokenBean.getId());
			}
		});
	}
	
	@Cacheable(value="tokenUser", key="#id")
	public TokenBean queryById(String id) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT " + columns + " FROM MM_TOKEN WHERE id = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { id }, new TokenMapper());
	}

	public List<TokenBean> queryByUserId(int userId) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT " + columns + " FROM MM_TOKEN WHERE userId = ?";
		return (List<TokenBean>) jdbcTemplate.query(sql, new Integer[] { userId }, new TokenMapper());
	}
	
	public List<TokenBean> queryByUserIdOfNoSolu(int userId) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT " + columns + " FROM MM_TOKEN T WHERE userId = ? AND NOT EXISTS (SELECT 1 FROM mm_token_solu TS WHERE TS.tokenid=t.id)";
		return (List<TokenBean>) jdbcTemplate.query(sql, new Integer[] { userId }, new TokenMapper());
	}

	public List<TokenWithSoluBean> queryBySoluUserId(String option, int soluId, int userId) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "";
		if (option.equals("edit")) {
			sql = "SELECT T.id, T.name, T.type, T.userId, T.createTime, S.soluId, S.soluName "
					+ "  FROM MM_TOKEN T                                                     "
					+ "  LEFT JOIN (SELECT A.tokenId, B.id as soluId, B.name as soluName     "
					+ "               FROM MM_TOKEN_SOLU A, MM_SOLUTION B                    "
					+ "              WHERE A.soluId = ?                                      "
					+ "                AND A.soluId = B.id) S ON S.tokenId = T.id            "
					+ " WHERE T.userId = ?                                                   ";
		} else if (option.equals("query")) {
			sql = "SELECT T.id, T.name, T.type, T.userId, T.createTime, S.soluId, S.soluName "
					+ "  FROM MM_TOKEN T,                                           "
					+ "       (SELECT A.tokenId, B.id as soluId, B.name as soluName "
					+ "          FROM MM_TOKEN_SOLU A, MM_SOLUTION B                "
					+ "         WHERE A.soluId = ?                                  "
					+ "           AND A.soluId = B.id) S                            "
					+ " WHERE S.tokenId = T.id                                      "
					+ "   AND T.userId = ?                                          ";
		} else {
			return null;
		}

		return (List<TokenWithSoluBean>) jdbcTemplate.query(sql, new Object[] { soluId, userId },
				new TokenWithSoluMapper());
	}
	
	public int insertTokenSolu(TokenSolusIdBean tokenSolusBean) {
		try {
			jdbcTemplate.setDataSource(pgDataSource);

			jdbcTemplate.execute("begin");

			List<String> tokenIds = tokenSolusBean.getTokenIds();
			for (String tokenId : tokenIds) {
				List<Integer> soluIds = tokenSolusBean.getSoluIds();
				for (int soluId : soluIds) {
					jdbcTemplate.update("INSERT INTO MM_TOKEN_SOLU (tokenId, soluId) VALUES(?, ?)",
							new Object[] { tokenId, soluId });
					forClearCache(tokenId, soluId);
				}
			}
			jdbcTemplate.execute("commit");
			return 0;
		} catch (Exception e) {
			jdbcTemplate.execute("rollback");
			e.printStackTrace();
			return -1;
		}
	}
	


	public int deleteByTokenId(String tokenId) {
		jdbcTemplate.setDataSource(pgDataSource);
		return jdbcTemplate.update("DELETE FROM MM_TOKEN_SOLU WHERE tokenId = ?", new Object[] { tokenId },
				new int[] { java.sql.Types.VARCHAR });
	}

	public int deleteBySoluId(int soluId) {
		jdbcTemplate.setDataSource(pgDataSource);
		return jdbcTemplate.update("DELETE FROM MM_TOKEN_SOLU WHERE soluId = ?", new Object[] { soluId },
				new int[] { java.sql.Types.INTEGER });
	}
	
	@CacheEvict(value="tokenSolu")
	public void forClearCache(String tokenId, int soluId){
		
	}
	
	@Cacheable(value="tokenSolu")
	public int queryTokenSolu(String tokenId, int soluId) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT count(*) FROM MM_TOKEN_SOLU WHERE tokenId = ? AND soluId = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { tokenId, soluId }, Integer.class);
	}

	public List<String> queryTokenSoluBySoluId(int soluId) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT tokenId FROM MM_TOKEN_SOLU WHERE soluId = ?";
		return jdbcTemplate.query(sql, new Object[] { soluId }, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				String tokenId = rs.getString("tokenId");
				return tokenId;
			}
		});
	}

	protected class TokenMapper implements RowMapper<TokenBean> {
		public TokenBean mapRow(ResultSet rs, int rowNum) throws SQLException {
			TokenBean token = new TokenBean();
			token.setId(rs.getString("id"));
			token.setName(rs.getString("name"));
			token.setType(rs.getInt("type"));
			token.setUserId(rs.getInt("userId"));
			token.setCreateTime(rs.getString("createTime"));
			return token;
		}
	}

	protected class TokenWithSoluMapper implements RowMapper<TokenWithSoluBean> {
		public TokenWithSoluBean mapRow(ResultSet rs, int rowNum) throws SQLException {
			TokenWithSoluBean token = new TokenWithSoluBean();
			token.setId(rs.getString("id"));
			token.setName(rs.getString("name"));
			token.setType(rs.getInt("type"));
			token.setUserId(rs.getInt("userId"));
			token.setCreateTime(rs.getString("createTime"));
			token.setSoluId(rs.getInt("soluId"));
			token.setSoluName(rs.getString("soluName"));
			return token;
		}
	}
}
